﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using Maticsoft.DBUtility;
using System.Web;
using MySql.Data.MySqlClient;
using EnvironmentManagerSystem.MODE;

namespace EnvironmentManagerSystem.BLL
{
    public class HuanPingService
    {
        #region 添加用时(x)
          public static bool Prosj(string sj,string pj)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_huanping set liuchengshijian='{0}' WHERE ProjectName = '{1}'",
                                                        sj, pj);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region 添加项目(x)
        //添加项目
        public static bool AddHuanPing(string ProjectID, string ProjectName)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" insert into tbs001_huanping (ProjectID,ProjectName) values ( ");
                br.AppendFormat("'{0}',", ProjectID);
                br.AppendFormat("'{0}'", ProjectName);
                br.AppendFormat(" )");
                return DbHelperMySQL.ExecuteSql(br.ToString()) > 0 ? true : false;
            }
            catch { return false; }
        }
        #endregion

        #region 删除项目(x)
        public static int DELHuanPing(string ProjectName)
        {
            int i = 0;
            try
            {
                i = DbHelperMySQL.ExecuteSql(string.Format(" delete from tbs001_huanping where ProjectName='{0}'", ProjectName));
            }
            catch (Exception ex)
            {

            }
            return i;
        }
        #endregion

        #region 获取用户列表(x)
        public static DataTable GetAllTable()
        {
            try
            {
                StringBuilder brb = new StringBuilder();
                brb.AppendFormat("select ID,ProjectName from tbs001_huanping");
                return DbHelperMySQL.Query(brb.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion

        #region 根据条件获取项目列表(x)
        public static DataTable GetTabless(string where)
        {
            try
            {
                StringBuilder brb = new StringBuilder();
                brb.AppendFormat("SELECT ProjectName,ID,jinjiqingkuang FROM tbs001_huanping WHERE 1=1 {0}", where);
                return DbHelperMySQL.Query(brb.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion

        #region 确认流程(项目受理、人员分配、起草文件、上传厅长、厅长签发)(x)
        //项目受理
        public static bool ProAcceptance(int cailiaoqiquan, string jinjiqingk)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_huanping set shoulituihui=1, xiangmushouliren='{0}',xiangmushoulishijians = '{1}',cailiaoqiquan={2},jinjiqingkuang='{3}',shouliTrace =CONCAT(shouliTrace,'|{0}已于{1}进行项目受理信息操作)'),dangqianliucheng='项目受理' WHERE ProjectName = '{4}'",
                                                        HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"), cailiaoqiquan, jinjiqingk, HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        //人员分配
        public static bool Pro(string renyuanfeipei)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_huanping set querenfenpei=1, renyuanfenpeirenyuan='{0}',fenpeidate = '{1}',renyuanfeipei='{2}',renyuanTrace =CONCAT(renyuanTrace,'|{0}已于{1}进行人员分配信息操作)'),dangqianliucheng='人员分配' WHERE ProjectName = '{3}'",
                                                        HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"), renyuanfeipei, HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        //起草批复
        public static bool Prowenjian(string Qicaojilu)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_huanping set wenjian=1, qicaowenjianrenyuan='{0}',qicaowenjianshijian = '{1}',qicaojilu='{2}',wenjianTrace =CONCAT(wenjianTrace,'|{0}已于{1}进行起草批复信息操作)'),dangqianliucheng='起草批复' WHERE ProjectName = '{3}'",
                                                        HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"), Qicaojilu, HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        //处长审查
        public static bool ProShangchuan(string Chuzhangjilu)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_huanping set querenshangchuan=1, shangchuantingzrenyuan='{0}',shangchuantingzhangshijian = '{1}',chuzhangjilu='{2}',querenTrace =CONCAT(querenTrace,'|{0}已于{1}进行处长审查信息操作)'),dangqianliucheng='处长审查' WHERE ProjectName = '{3}'",
                                                        HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"), Chuzhangjilu, HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        //厅长签发
        public static bool ProQianfa(string Tingzhangjilu)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_huanping set qianfa=1, tingzhangqianfaren='{0}',tingzhangqianfashijian = '{1}',tingzhanglilu='{2}',qianfaTrace =CONCAT(qianfaTrace,'|{0}已于{1}进行厅长签发信息操作)'),dangqianliucheng='厅长签发' WHERE ProjectName = '{3}'",
                                                        HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"), Tingzhangjilu, HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region 受理数据编辑(x)
        public static string GetCount(string CName)
        {
            MySqlDataReader i = null;
            string re = "";
            try
            {
                i = DbHelperMySQL.ExecuteReader(CName);
                if (i.Read())
                {
                    re = i[0].ToString();
                }
            }
            catch (Exception ex)
            {
            }
            return re;
        }
        #endregion

        #region 根据项目名称获取项目信息(x)
        public static DataTable GetProjectInfo()
        {
            try
            {
                string strSql = string.Format(@"SELECT tbs001_huanping.* FROM tbs001_huanping where ProjectName='{0}'", HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.Query(strSql).Tables[0];
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region 根据项目名称获取选中环评信息(x)
        public static DataTable GetProjects()
        {
            try
            {
                string strSql = string.Format(@"SELECT tbs001_developprojectbasicinfo.* FROM tbs001_developprojectbasicinfo where ProjectName='{0}'", HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.Query(strSql).Tables[0];
            }
            catch
            {
                return null;
            }
        }

        #endregion

        #region 项目备注、项目日志(x)
        //项目备注
        public static bool UpdateProjectRemark(string Remark)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_huanping set ProjectBz='{0}',xiangmubeizhuren = '{1}',xiangmubeizhushijian='{2}',MemoTrace =CONCAT(MemoTrace,'|{1}已于{2}进行项目备注信息修改操作)') WHERE ProjectName = '{3}'",
                                                       Remark, HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"), HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        //项目日志
        public static bool UpdateProjectLog(string Log)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_huanping set ProjectLog='{0}',xiangmurizhiren = '{1}',xiangmurizhishijian='{2}',LogTrace =CONCAT(LogTrace,'|{1}已于{2}进行项目日志信息修改操作)') WHERE ProjectName = '{3}'",
                                                        Log, HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"), HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region  项目通知总耗费天数(x)
        public static int ProNoticeDays(string StepDate, string AcceptanceDate)
        {
            string strSql = string.Format(@"SELECT MIN(DATE) AS startdate,enddate FROM tbs001_huanping WHERE ProjectName = '{0}' 
            AND enddate <= '{1}' AND DATE >= '{2}' GROUP BY enddate", HttpContext.Current.Session["pjName"].ToString(), StepDate, AcceptanceDate);
            DataTable dt = DbHelperMySQL.Query(strSql).Tables[0];
            int NoticeDays = 0;
            foreach (DataRow dr in dt.Rows)
            {
                NoticeDays += (Convert.ToDateTime(dr[1]) - Convert.ToDateTime(dr[0])).Days;
            }
            return NoticeDays;
        }
        public static int ProNoticeDaysss(string StepDate, string AcceptanceDate, string pn)
        {
            string strSql = string.Format(@"SELECT MIN(DATE) AS startdate,enddate FROM tbs001_huanping WHERE ProjectName = '{0}' 
            AND enddate <= '{1}' AND DATE >= '{2}' GROUP BY enddate", pn, StepDate, AcceptanceDate);
            DataTable dt = DbHelperMySQL.Query(strSql).Tables[0];
            int NoticeDays = 0;
            foreach (DataRow dr in dt.Rows)
            {
                NoticeDays += (Convert.ToDateTime(dr[1]) - Convert.ToDateTime(dr[0])).Days;
            }
            return NoticeDays;
        }
        #endregion

        #region 根据项目名称查询项目信息(x)
        public static HuanPingDataCollection GetByProjectName(string where)
        {
            HuanPingDataCollection hs = new HuanPingDataCollection();
            try
            {
                StringBuilder br = new StringBuilder();
                //项目受理时间、受理状态、人员分配时间、人员分配状态、起草文件时间、起草文件状态、起草文件人员、上传厅长时间、上传厅长状态、上传厅长人员
                //厅长签发时间、厅长签发状态、厅长签发人员
                br.Append(" SELECT xiangmushoulishijians,Shoulituihui,fenpeidate,querenfenpei,qicaowenjianshijian,wenjian,qicaowenjianrenyuan,shangchuantingzhangshijian,querenshangchuan,shangchuantingzrenyuan,");
                br.Append(" tingzhangqianfashijian,qianfa,tingzhangqianfaren");
                br.Append(" FROM tbs001_huanping ");
                br.AppendFormat(" where ProjectName like '{0}' ", where);
                MySqlDataReader dr = DbHelperMySQL.ExecuteReader(br.ToString());
                while (dr.Read())
                {
                    HuanPingData model = new HuanPingData();
                    if (dr[0] != DBNull.Value)
                        model.xiangmushoulishijians = dr[0].ToString();//受理时间
                    if (dr[1] != DBNull.Value)
                        model.Shoulituihui = Convert.ToInt32(dr[1]);//受理状态
                    if (dr[2] != DBNull.Value)
                        model.Fenpeidate = dr[2].ToString();//人员分配时间
                    if (dr[3] != DBNull.Value)
                        model.Querenfenpei = Convert.ToInt32(dr[3]);//人员分配状态
                    if (dr[4] != DBNull.Value)
                        model.Qicaowenjianshijian = dr[4].ToString();//起草文件时间
                    if (dr[5] != DBNull.Value)
                        model.Wenjian = Convert.ToInt32(dr[5]);//起草文件状态
                    if (dr[6] != DBNull.Value)
                        model.Qicaowenjianrenyuan = dr[6].ToString();//起草文件人员
                    if (dr[7] != DBNull.Value)
                        model.Shangchuantingzhangshijian = dr[7].ToString();//上传厅长时间
                    if (dr[8] != DBNull.Value)
                        model.Querenshangchuan = Convert.ToInt32(dr[8]);//上传厅长状态
                    if (dr[9] != DBNull.Value)
                        model.Shangchuantingzrenyuan = dr[9].ToString();//上传厅长人员
                    if (dr[10] != DBNull.Value)
                        model.Tingzhangqianfashijian = dr[10].ToString();//上传厅长时间
                    if (dr[11] != DBNull.Value)
                        model.Qianfa = Convert.ToInt32(dr[11]);//上传厅长状态
                    if (dr[12] != DBNull.Value)
                        model.Tingzhangqianfaren = dr[12].ToString();//上传厅长人员
                    hs.Add(model);
                }
                return hs;
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region MyRegion(x)
        public static DevelopprojectbasicinfoDataCollection GetBySelectID(string IDlist)
        {
            DevelopprojectbasicinfoDataCollection hs = new DevelopprojectbasicinfoDataCollection();
            try
            {
                string sql = @"SELECT ID,ProjectName,reportstate,reportpeople,reportdate FROM tbs001_developprojectbasicinfo WHERE ID IN (" + IDlist + ")";
                MySqlDataReader dr = DbHelperMySQL.ExecuteReader(sql);
                while (dr.Read())
                {
                    DevelopprojectbasicinfoData model = new DevelopprojectbasicinfoData();
                    JianshehuanjingupModels jj = new JianshehuanjingupModels();
                    if (dr[0] != DBNull.Value)
                        model.ID = Convert.ToInt32(dr[0]);//ID
                    if (dr[1] != DBNull.Value)
                        model.ProjectName = dr[1].ToString();//项目名称                    
                    if (dr[2] != DBNull.Value)
                        model.reportstate = Convert.ToInt32(dr[2]);//汇总上报状态1上报2退回3上报到环境
                    if (dr[3] != DBNull.Value)
                        model.reportpeople = dr[3].ToString();//汇总上报执行人
                    if (dr[4] != DBNull.Value)
                        model.reportdate = dr[4].ToString();//汇总上报日期
                    hs.Add(model);
                }
                return hs;
            }
            catch { return null; }
        }
        #endregion

        #region 汇总上报状态1上报2退回3上报到环境(x)
        public static bool Updatereportstate(string ID, string ProjectName)
        {
            try
            {
                string strSql = string.Format("UPDATE tbs001_developprojectbasicinfo set reportstate=3 WHERE ID={0} AND ProjectName = '{1}'", ID, ProjectName);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region 查询所有
        public static DataTable GetSelectAll()
        {
            try
            {
                StringBuilder brb = new StringBuilder();
                brb.AppendFormat(@"SELECT a.*,b.*,c.ID,c.NAME AS AClassName,d.UnitName AS dd 
                                  FROM tbs001_huanping a,tbs001_developprojectbasicinfo b,tbs001_constructionunit c,tbs001_evaluationunit d
                                  WHERE a.ProjectID = b.ID AND b.js = c.ID AND b.hp = d.ID ");
                //AND a.tingzhangqianfaren != ''  AND a.tingzhangqianfashijian != ''
                return DbHelperMySQL.Query(brb.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion

        #region 查询已受理未办结(x)
        public static DataTable GetSelectsl()
        {
            try
            {
                StringBuilder brb = new StringBuilder();
                brb.AppendFormat(@"SELECT a.*,b.*,c.ID,c.NAME AS AClassName,d.UnitName AS dd,e.DataValue AS hplb FROM tbs001_huanping a,
                                 tbs001_developprojectbasicinfo b,tbs001_constructionunit c,tbs001_evaluationunit d,tbs001_basicdatasub e 
                                 WHERE a.ProjectID = b.ID AND b.js = c.ID AND b.hp = d.ID AND b.huanpingleibie = e.ID AND a.shoulituihui = 1 
                                 AND qianfaTrace =''");
                return DbHelperMySQL.Query(brb.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion

        #region 踏查15天(x)
        public static DevelopprojectbasicinfoDataCollection GetTachariqis(string where)
        {
            DevelopprojectbasicinfoDataCollection hs = new DevelopprojectbasicinfoDataCollection();
            try
            {
                string sql = @"SELECT ProjectName,tachadidian FROM tbs001_developprojectbasicinfo WHERE ReportingTrace='' and tachariqi='" + where + "'";
                MySqlDataReader dr = DbHelperMySQL.ExecuteReader(sql);
                while (dr.Read())
                {
                    DevelopprojectbasicinfoData model = new DevelopprojectbasicinfoData();
                    if (dr[0] != DBNull.Value)
                        model.ProjectName = dr[0].ToString();
                    if (dr[1] != DBNull.Value)
                        model.tachadidian = dr[1].ToString();
                    hs.Add(model);
                }
                return hs;
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region 会议15天(x)
        public static DevelopprojectbasicinfoDataCollection GetZhuanjiahuiyis(string where)
        {
            DevelopprojectbasicinfoDataCollection hs = new DevelopprojectbasicinfoDataCollection();
            try
            {
                string sql = @"SELECT ProjectName,zhuanjiahuiyididian,zhuanjiahuiyiriqiflag FROM tbs001_developprojectbasicinfo WHERE ReportingTrace='' AND zhuanjiahuiyichuliriqi='" + where + "'";
                MySqlDataReader dr = DbHelperMySQL.ExecuteReader(sql);
                while (dr.Read())
                {
                    DevelopprojectbasicinfoData model = new DevelopprojectbasicinfoData();
                    if (dr[0] != DBNull.Value)
                        model.ProjectName = dr[0].ToString();
                    if (dr[1] != DBNull.Value)
                        model.zhuanjiahuiyididian = dr[1].ToString();
                    if (dr[2] != DBNull.Value)
                        model.zhuanjiahuiyiriqiflag = Convert.ToInt32(dr[2]);
                    hs.Add(model);
                }
                return hs;
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region 查询是否有重复项目名称(x)
        public static DataTable SelectPjName(string PjName)
        {
            try
            {
                StringBuilder brb = new StringBuilder();
                brb.AppendFormat("select * from  tbs001_developprojectbasicinfo where ProjectName = '{0}'", PjName);
                return DbHelperMySQL.Query(brb.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion
    }
}